/**
 * 
 */
package com.feib.stms.dao.jdbc;

import java.sql.Types;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;

import com.feib.stms.dao.DeliveryPaymentChkSumDao;
import com.feib.stms.dao.jdbc.rowmapper.DeliveryPaymentChkSumRowMapper;
import com.feib.stms.model.DeliveryPaymentChk;
import com.feib.stms.model.DeliveryPaymentChkSum;
import com.feib.stms.model.Group;
import com.feib.stms.model.UploadFile;
import com.feib.stms.pagination.PaginatedList;

/**
 * @author Jimmy Liu
 *
 */
@Repository("deliveryPaymentChkSumDao")
public class DeliveryPaymentChkSumJdbcDao extends BaseJdbcDao implements DeliveryPaymentChkSumDao {

	/* (non-Javadoc)
	 * @see com.feib.stms.dao.DeliveryPaymentChkSumDao#deleteByTempUploadFilePk(java.lang.Long)
	 */
	public int deleteByTempUploadFilePk(Long pk) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("DELETE FROM TEMP_DELIVERY_CHK_SUM WHERE TEMP_UPLOAD_FILE_PK = :TEMP_UPLOAD_FILE_PK");
		parameters.addValue("TEMP_UPLOAD_FILE_PK", pk, Types.NUMERIC);

		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
	}

	public PaginatedList<DeliveryPaymentChkSum> findPaginatedListByPaymentFileContent(
			DeliveryPaymentChk entity, Date deliveryDateFrom,
			Date deliveryDateTo, String amountStatus,
			List<String> constrainSecuritiesNos,
			PaginatedList<DeliveryPaymentChkSum> paginatedList) {


		SqlQueryMapProvider sqlQueryMapProvider = this.getSqlQueryMapProviderByPaymentFileContent(entity, deliveryDateFrom, deliveryDateTo, amountStatus, constrainSecuritiesNos, paginatedList);

		PaginatedList<DeliveryPaymentChkSum> result =  this.queryForPaginatedList(sqlQueryMapProvider, new DeliveryPaymentChkSumRowMapper(), paginatedList.getPageNumber(), paginatedList.getPageSize());

		/* 最後一頁，計算總計 */
		if (null != result && result.isLastPage())
		{
			sqlQueryMapProvider = getTotalSqlQueryMapProviderByPaymentFileContent(entity, deliveryDateFrom, deliveryDateTo, amountStatus, constrainSecuritiesNos);

			DeliveryPaymentChkSum totalObject = this.getSimpleJdbcTemplate().queryForObject(sqlQueryMapProvider.getSql(),new DeliveryPaymentChkSumRowMapper(), sqlQueryMapProvider.getParameters());

			result.setTotalObject(totalObject);
		}

		return result;

	}

	public SqlQueryMapProvider getSqlQueryMapProviderByPaymentFileContent(
			DeliveryPaymentChk entity, Date deliveryDateFrom,
			Date deliveryDateTo, String amountStatus,
			List<String> constrainSecuritiesNos,
			PaginatedList<DeliveryPaymentChkSum> paginatedList) {

		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();
		sql.append("SELECT D.*");
		sql.append(", CT.CODE_NAME AS DEAL_RESULT_CODE_NAME");
		sql.append(", S.SECURITIES_NAME AS SECURITIES_NAME");
		sql.append(" FROM DELIVERY_CHK_SUM D");
		sql.append(" LEFT JOIN CODE_TABLE CT ON CT.CODE_KIND = 'DEAL_RESULT_CODE' AND CT.CODE_VALUE = D.DEAL_RESULT_CODE");
		sql.append(" LEFT JOIN SECURITIES S ON S.SECURITIES_NO = D.SECURITIES_NO");
		sql.append(" WHERE 1 = 1 ");

		// 狀態 , 1:不足款 else 全部
		if ("1".equalsIgnoreCase(amountStatus))
		{
			sql.append(" AND D.LESS_AMOUNT > 0");
		}

		// 可查詢的分公司
		if (CollectionUtils.isNotEmpty(constrainSecuritiesNos))
		{
			if (1 == constrainSecuritiesNos.size())
			{
				sql.append(" AND D.SECURITIES_NO = :SECURITIES_NO");
				parameters.put("SECURITIES_NO",constrainSecuritiesNos.get(0) );
			}
			else
			{
				sql.append(" AND D.SECURITIES_NO IN (:SECURITIES_NOS)");
				parameters.put("SECURITIES_NOS",constrainSecuritiesNos );
			}

		}

		/* 上傳日期 */
		if (null != deliveryDateFrom && null != deliveryDateTo)
		{
			// between 上傳起迄
			sql.append(" AND TO_CHAR(D.DELIVERY_DATE, 'YYYYMMDD') >= TO_CHAR(:DELIVERY_DATE_FROM, 'YYYYMMDD')");
			sql.append(" AND TO_CHAR(D.DELIVERY_DATE, 'YYYYMMDD') <= TO_CHAR(:DELIVERY_DATE_TO, 'YYYYMMDD')");
			parameters.put("DELIVERY_DATE_FROM", deliveryDateFrom );
			parameters.put("DELIVERY_DATE_TO", deliveryDateTo);

		}
		else if (null != deliveryDateFrom)
		{
			// >=上傳起
			sql.append(" AND TO_CHAR(D.DELIVERY_DATE, 'YYYYMMDD') >= TO_CHAR(:DELIVERY_DATE_FROM, 'YYYYMMDD') ");
			parameters.put("UPLOAD_DATE_FROM", deliveryDateFrom);

		}
		else if (null != deliveryDateTo)
		{
			// <= 上傳迄
			sql.append(" AND TO_CHAR(D.DELIVERY_DATE, 'YYYYMMDD') <= TO_CHAR(:DELIVERY_DATE_TO, 'YYYYMMDD') ");
			parameters.put("DELIVERY_DATE_TO", deliveryDateTo);

		}
		else if (null != entity && null != entity.getDeliveryDate())
		{
			// 單一日期
			sql.append(" AND TO_CHAR(D.DELIVERY_DATE, 'YYYYMMDD') = TO_CHAR(:DELIVERY_DATE, 'YYYYMMDD' )");
			parameters.put("DELIVERY_DATE", entity.getDeliveryDate());

		}


		if (null != entity)
		{
			/* 營業員 */
			if (StringUtils.isNotEmpty(entity.getTeller()))
			{
				sql.append(" AND D.TELLER like :TELLER");
				parameters.put("TELLER", entity.getTeller()+"%" );
			}

			/* 客戶戶名 */
			if (StringUtils.isNotEmpty(entity.getCustomerName()))
			{
				sql.append(" AND D.CUSTOMER_NAME like :CUSTOMER_NAME");
				parameters.put("CUSTOMER_NAME", entity.getCustomerName()+"%" );
			}

			/* 所屬群組(證券公司) */
			Group group = entity.getGroup();
			if (null != group && null != group.getGroupPk())
			{
				sql.append(" AND D.GROUP_PK = :GROUP_PK");
				parameters.put("GROUP_PK", group.getGroupPk() );
			}

			/* 所屬檔案 */
			UploadFile uploadFile = entity.getUploadFile();
			if (null != uploadFile &&  null != uploadFile.getUploadFilePk())
			{
				sql.append(" AND D.UPLOAD_FILE_PK = :UPLOAD_FILE_PK");
				parameters.put("UPLOAD_FILE_PK", uploadFile.getUploadFilePk());
			}
		}


		if (null != paginatedList)
		{
			String orderBy = paginatedList.getSortCriterion();
			if (StringUtils.isNotEmpty(orderBy))
			{
				if (!orderBy.toUpperCase().startsWith("D."))
				{
					orderBy = "D." + orderBy;
				}

				sql.append(" ORDER BY ").append(orderBy).append(" ").append( 2==paginatedList.getSortDirection() ? "ASC" : "DESC" );
			}			
		}

		SqlQueryMapProvider sqlQueryMapProvider = new SqlQueryMapProvider(sql.toString(), parameters);

		return sqlQueryMapProvider;

	}
	
	public SqlQueryMapProvider getTotalSqlQueryMapProviderByPaymentFileContent(DeliveryPaymentChk entity, Date deliveryDateFrom, Date deliveryDateTo, String amountStatus, List<String> constrainSecuritiesNos)
	{

		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();

		sql.append("SELECT COUNT(*) AS RECORD_COUNT, SUM(sumRpt.NET_AMOUNT) AS NET_AMOUNT, SUM(sumRpt.LESS_AMOUNT) AS LESS_AMOUNT ");

		SqlQueryMapProvider tmp = getSqlQueryMapProviderByPaymentFileContent(entity, deliveryDateFrom, deliveryDateTo, amountStatus, constrainSecuritiesNos, null);
		sql.append(" from ( ");
		sql.append(tmp.getSql());
		sql.append(" ) sumRpt ");

		parameters.putAll(tmp.getParameters());

		SqlQueryMapProvider sqlQueryMapProvider = new SqlQueryMapProvider(sql.toString(), parameters);
		
		logger.debug("{}", sqlQueryMapProvider);

		return sqlQueryMapProvider;
	}

}
